<?php
/*
 * Copyright 2008 by Oliver Radwan, Maxwell Palmer, Nolan McNair,
 * Taylor Talmage, and Allen Tucker.  This program is part of RMH Homebase.
 * RMH Homebase is free software.  It comes with absolutely no warranty.
 * You can redistribute it and/or modify it under the terms of the GNU
 * General Public License as published by the Free Software Foundation
 * (see <http://www.gnu.org/licenses/ for more information).
*/
include_once('dbinfo.php');
/**
 * Functions to create, update, and retrieve information from the
 * dbSchedules table in the database.  This table contains the master
 * schedules for volunteers that are maintained by the House Manager.
 * These schedules provide the basis for generating each new month's
 * calendar, which is a series of shifts in the dbShifts table.
 * @version May 1, 2008
 * @author Allen Tucker and Maxwell Palmer
 */

/* Create an empty table of shifts into which a volunteer can be scheduled.
 * venue = "Rec", "Fam", "Van", "Kit", "Off", "Lin", "Chr", "Dup"
 * group = "One" (or "Two" for Rec and Fam)
 * day = "Mon", "Tue", ... "Sun"
 * time = "9-12", "12-15", "15-18", "18-21", "10-13", "13-16", "14-17, or "17-21"
 * slots = the number of slots to be filled for this shift
 * persons is a comma-separated list of ids, like "alex2071234567"
 * notes = any notes that will be displayed for this shift on the schedule
 *
 */
	function setup_dbSchedules () {
		connect();
		mysql_query("DROP TABLE IF EXISTS dbSchedules");
		$result = mysql_query("CREATE TABLE dbSchedules (venue TEXT, my_group TEXT, day TEXT, time TEXT, slots INT, persons TEXT, notes TEXT)");
        if (!$result)
		    die ("Could not create dbSchedules table: " . mysql_error());
	    $venues = array("Rec", "Fam", "Dup");
		$days = array("Mon","Tue","Wed","Thu","Fri","Sat","Sun");
	    $times = array("9-12","12-15","15-18","18-21");
		$prefix = "INSERT INTO dbSchedules VALUES (";
		mysql_query($prefix . "'" . "Lin" . "', " . "'One', '" . "Mon" . "', '" . "9-12" . "', '" . 2 . "', '', '')");
		mysql_query($prefix . "'" . "Lin" . "', " . "'One', '" . "Thu" . "', '" . "9-12" . "', '" . 3 . "', '', '')");
/*		mysql_query($prefix . "'" . "Lin" . "', " . "'Two', '" . "Mon" . "', '" . "9-12" . "', '" . 2 . "', '', '')");
		mysql_query($prefix . "'" . "Lin" . "', " . "'Two', '" . "Thu" . "', '" . "9-12" . "', '" . 3 . "', '', '')"); */
		foreach ($days as $weekday) {
		    mysql_query($prefix . "'" . "Kit" . "', " . "'One', '" . $weekday . "', '" . "9-12" . "', '" . 1 . "', '', '')");
			mysql_query($prefix . "'" . "Kit" . "', " . "'One', '" . $weekday . "', '" . "18-21" ."', '" . 1 . "', '', '')");
			mysql_query($prefix . "'" . "Van" . "', " . "'One', '" . $weekday . "', '" . "9-12" . "', '" . 1 . "', '', '')");
/*			mysql_query($prefix . "'" . "Kit" . "', " . "'Two', '" . $weekday . "', '" . "9-12" . "', '" . 1 . "', '', '')");
			mysql_query($prefix . "'" . "Kit" . "', " . "'Two', '" . $weekday . "', '" . "18-21" ."', '" . 1 . "', '', '')");
			mysql_query($prefix . "'" . "Van" . "', " . "'Two', '" . $weekday . "', '" . "9-12" . "', '" . 1 . "', '', '')"); */
			if ($weekday!=="Sat" && $weekday!=="Sun") {
				mysql_query($prefix . "'" . "Off" . "', " . "'One', '" . $weekday . "', '" . "9-12" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Off" . "', " . "'One', '" . $weekday . "', '" . "12-15" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Van" . "', " . "'One', '" . $weekday . "', '" . "12-15" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Van" . "', " . "'One', '" . $weekday . "', '" . "15-18" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Van" . "', " . "'One', '" . $weekday . "', '" . "18-21" . "', '" . 1 . "', '', '')");
/*				mysql_query($prefix . "'" . "Off" . "', " . "'Two', '" . $weekday . "', '" . "9-12" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Off" . "', " . "'Two', '" . $weekday . "', '" . "12-15" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Van" . "', " . "'Two', '" . $weekday . "', '" . "12-15" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Van" . "', " . "'Two', '" . $weekday . "', '" . "15-18" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Van" . "', " . "'Two', '" . $weekday . "', '" . "18-21" . "', '" . 1 . "', '', '')"); */
			}
		}
		foreach ($days as $weekday) {
				mysql_query($prefix . "'" . "Chr" . "', " . "'One', '" . $weekday . "', '" . "12-14" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Chr" . "', " . "'One', '" . $weekday . "', '" . "14-16" . "', '" . 1 . "', '', '')");
		        mysql_query($prefix . "'" . "Chr" . "', " . "'One', '" . $weekday . "', '" . "16-18" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Chr" . "', " . "'One', '" . $weekday . "', '" . "18-20" . "', '" . 1 . "', '', '')");
/*				mysql_query($prefix . "'" . "Chr" . "', " . "'Two', '" . $weekday . "', '" . "12-14" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Chr" . "', " . "'Two', '" . $weekday . "', '" . "14-16" . "', '" . 1 . "', '', '')");
		        mysql_query($prefix . "'" . "Chr" . "', " . "'Two', '" . $weekday . "', '" . "16-18" . "', '" . 1 . "', '', '')");
			    mysql_query($prefix . "'" . "Chr" . "', " . "'Two', '" . $weekday . "', '" . "18-20" . "', '" . 1 . "', '', '')"); */
		}
		foreach ($venues as $venue) {
		  if ($venue=="Dup") $count = 1;
		  else $count = 3;
		  foreach ($days as $weekday) {
		    foreach ($times as $time) {
		  	    mysql_query($prefix . "'" . $venue . "', " . "'One', '" . $weekday . "', '" . $time . "', '" . $count . "', '', '')");
	 	    if ($venue=="Rec" || $venue=="Fam")
		  	    mysql_query($prefix . "'" . $venue . "', " . "'Two', '" . $weekday . "', '" . $time . "', '" . $count . "', '', '')");
		  
		    }
		  }
		}
		mysql_close();
	}
	
	function insert_master_shift($venue, $group, $day, $time, $slots) {
		$time_start = substr($time, 0, strpos($time, "-"));
		$time_end = substr($time, strpos($time, "-")+1);
		$other_shifts = get_master_shifts($venue, $group, $day);
		foreach ($other_shifts as $other_shift) { 
			$o_start = substr($other_shift['time'], 0, strpos($other_shift['time'], "-"));
			$o_end = substr($other_shift['time'], strpos($other_shift['time'], "-")+1);
			if (masterslots_overlap($time_start, $time_end, $o_start, $o_end))
		        return false;
		}
		connect();
		$prefix = "INSERT INTO dbSchedules VALUES (";
		mysql_query($prefix . "'" . $venue . "', '" . $group . "', '" . $day . "', '" . $time . "', '" . $slots . "', '', '')");
		mysql_close();
		return true;
	}	

/**
 * @result == true if $s1's timeslot overlaps $s2's timeslot, and false otherwise.
 */
function masterslots_overlap($s1_start, $s1_end, $s2_start, $s2_end) {
	if ($s1_end > $s2_start) {
       if ($s1_start >= $s2_end)
          return false;
       else return true;
    }
    else return false;
}

function delete_master_shift ($venue, $group, $day, $time) {
	$s = get_master_shift($venue, $group, $day, $time);
	if (!$s)
	    return false;
	$person_ids = explode(',', $s['persons']);    // get an array of scheduled person id's
	foreach ($person_ids as $person_id)
	    if ($person_id != "")
	       unschedule_person ($venue, $group, $day, $time, $person_id); 
    connect();
    $query="DELETE FROM dbSchedules WHERE my_group = '" . $group . "' AND day = '" . $day . 
	            "' AND venue = '" . $venue . "' AND time = '" . $time . "'";
	$result=mysql_query($query);
	if(!$result) {
		echo "<br>unable to delete shift from Master Schedule ".mysql_error();
        mysql_close();
        return false;
	}
    mysql_close();
    return true;    
}
	
   /* return a row on the schedule table (called a "master_shift") as an associative array
    * for the given venue, group, day, and time (either start or start-end).
    *    The array is (venue, my_group, day, time, start, end, slots, persons, notes)
    *    and persons is a comma-separated string of ids, like "alex2077291234"
    * if none exists, return false
    */
   function get_master_shift ($venue, $group, $day, $time) {
   	   connect();
	   if (!strpos($time,"-")) {
   	      $query = "SELECT * FROM dbSchedules WHERE my_group = '" . $group . "' AND day = '" . $day . 
  	            "' AND venue = '" . $venue . "' AND time LIKE '%" . $time . "-%' ";
   	   }
	   else 
	      $query = "SELECT * FROM dbSchedules WHERE my_group = '" . $group . "' AND day = '" . $day . 
	            "' AND venue = '" . $venue . "' AND time = '" . $time . "'";
	   $result = mysql_query($query);
	   mysql_close();
	   if (mysql_num_rows($result)==0) 
	       return false;   
	   $resultarray = mysql_fetch_array($result, MYSQL_ASSOC); 
	   $i = strpos($resultarray["time"],"-");
	   $resultarray["start"] = substr($resultarray["time"],0,$i);
	   $resultarray["end"] = substr($resultarray["time"],$i+1); 
   	   return $resultarray;
   }
   /*
     * @return all master schedule entries for a particular venue and day
     * Each row in the array is an associative array
     *    of (venue, my_group, day, time, start, end, slots, persons, notes)
     *    and persons is a comma-separated string of ids, like "alex2077291234"
     * If there are no entries, return false
     */
    function get_master_shifts($venue,$group,$day) {
		connect();
		$query="SELECT * FROM dbSchedules WHERE my_group = '" . $group . "' AND day = '" . $day . 
  	            "' AND venue = '" . $venue . "'";
		$result = mysql_query($query);
		mysql_close();
		if (mysql_num_rows($result)==0) return false;   
	    $outcome = array();
		for($i=0;$i<mysql_num_rows($result);$i++) {
			$outcome[] = mysql_fetch_array($result, MYSQL_ASSOC);
		}
		return $outcome;
	}
   
   /* schedule a person for a given day and time and venue in group One or Two
    * update that persons schedule in the dbPersons table
    *
    */
	function schedule_person($venue, $group, $day, $time, $person_id){
		connect();
		if ($venue=="Chr") {  // translate $time to something that can be matched
		    switch ($time) {
		        case "12-14":
		        case "14-16": $chrtime = "12-15"; break;
		        case "16-18": $chrtime = "15-18"; break;
		        default: $chrtime = "18-21";
		    }
		}
		else $chrtime = $time;
		$query1 = "SELECT * FROM dbSchedules WHERE venue = '" .
		                       $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$query2 = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
		$result = mysql_query($query1);
	    $resultp = mysql_query($query2);
	    if (!$result || !$resultp) die ("could not query the database");
	    // be sure the master shift and person both exist
	    if (mysql_num_rows($result) !== 1 || mysql_num_rows($resultp) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid or person not in database";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $resultp_row = mysql_fetch_array($resultp, MYSQL_ASSOC);
        $persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
        $schedule = explode(',', $resultp_row['schedule']); // get an array of person's scheduled times
        $availability = explode(',', $resultp_row['availability']);     // and their availabiltiy
        if (    // in_array(substr($day,0,3).$chrtime, $availability) &&
            !in_array($person_id, $persons) &&
            !in_array($group.substr($day,0,3).$time, $schedule)) {
           $persons[] = $person_id;             // add the person to the array, and
           $schedule[] = $venue . $group . $day . $time; // add the time to the person's schedule
           $result_row['persons'] = implode(',', $persons);     // and update one row in each table
           $resultp_row['schedule'] = implode(',', $schedule);  // in the database
           mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . 
                        "' WHERE venue = '" . $venue . "' AND my_group = '" .
                        $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
	       mysql_query("UPDATE dbPersons SET schedule = '" . $resultp_row['schedule'] .
	       		               "' WHERE id = '" . $person_id . "'");
           mysql_close();
           return "";
        }
        mysql_close();
        return "Error: can't schedule a person not available or already scheduled";
	}
    /* unschedule a volunteer from a venue and group at a given day and time
     * update that person's schedule in the dbPersons table
     *
     */
    function unschedule_person ($venue, $group, $day, $time, $person_id) {
    	connect();
		if ($venue=="Chr") {  // translate $time to something that can be matched
		    switch ($time) {
		        case "12-14": $chrtime = "12-15"; break;
		        case "14-16": $chrtime = "12-15"; break;
		        case "16-18": $chrtime = "15-18"; break;
		        default: $chrtime = "18-21";
		    }
		}
		else $chrtime = $time;
		$query = "SELECT * FROM dbSchedules WHERE venue = '" .
				               $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$queryp = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
		$result = mysql_query($query);
	    $resultp = mysql_query($queryp);
	    // be sure the person exists and is scheduled
	    if (!$result || mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid";
	    }
	    else if (!$resultp || mysql_num_rows($resultp) !== 1) {
	       $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	       $persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
           if (in_array($person_id, $persons)) {
				$index = array_search($person_id, $persons);
				array_splice($persons, $index, 1);               // remove the person from the array, and
				$result_row['persons'] = implode(',', $persons); // and update one row in the schedule
                mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . 
                        "' WHERE venue = '" . $venue . "' AND my_group = '" .
                        $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
           }
           mysql_close();
	       return "Error: person not in database" . $person_id;
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $resultp_row = mysql_fetch_array($resultp, MYSQL_ASSOC);
        $persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
        $schedule = explode(',', $resultp_row['schedule']); // get an array of person's scheduled times
        if (in_array($person_id, $persons) && in_array($venue.$group.$day.$time, $schedule)) {
           $index = array_search($person_id, $persons);
           $indexp = array_search($venue.$group.$day.$time, $schedule);
           array_splice($persons, $index, 1);   // remove the person from the array, and
           array_splice($schedule, $indexp, 1); // remove the time from the person's schedule
           $result_row['persons'] = implode(',', $persons);     // and update one row in each table
           $resultp_row['schedule'] = implode(',', $schedule);  // in the database
           mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . 
                       "' WHERE venue = '" . $venue . "' AND my_group = '" .
                        $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
	       mysql_query("UPDATE dbPersons SET schedule = '" . $resultp_row['schedule'] .
	       		               "' WHERE id = '" . $person_id . "'");
	       mysql_close();
           return "";
        }
        mysql_close();
 		return "Error: can't unschedule a person not scheduled";
    }
    /* insert a note in the schedule for a given venue, group, day, and time.
     *
     */
    function make_notes ($venue, $group, $day, $time, $notes) {
    	connect();
		$query = "SELECT * FROM dbSchedules WHERE venue = '" .
				               $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query);
	    if (!$result) die ("could not query the database");
	    // be sure the person exists and is scheduled
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $result_row['notes'] = $notes;
	    mysql_query("UPDATE dbSchedules SET notes = '". $result_row['notes'] . "' WHERE venue = '" .
				        $venue . "' AND my_group = '" .
                        $group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
	    mysql_close();
        return "";
    }
    /*
	 * @return whether or not a person is scheduled in a given venue, group, day and time
	 *
	 */
	function is_scheduled ($venue, $group, $day, $time, $person_id) {
		connect();
		$query = "SELECT * FROM dbSchedules WHERE venue = '" .
				               $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query);
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
		$persons = explode(',', $result_row['persons']);    // get array of scheduled person id's
        mysql_close();
        if (in_array($person_id, $persons))
           return true;
        else return false;
	}
	/*
	 * @return all persons scheduled for a particular venue, group, day, and time
	 * as an array of associative arrays.  Each associative array has
	 * entries indexed by the field names of a person in dbPersons.
	 */
	function get_persons($venue, $group, $day, $time) {
        connect();
		$query1 = "SELECT * FROM dbSchedules WHERE venue = '" .
				               $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
	    if (!$result) die ("could not query the database");
	    $out = array();
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       $out[] = "Error: group-day-time not valid";
	       return $out;
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $person_ids = explode(',', $result_row['persons']);    // get an array of scheduled person id's
	    foreach ($person_ids as $person_id)
	      if ($person_id!="") {
            $query2 = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
		    $resultp = mysql_query($query2);
            if (!$resultp) die ("could not query the database");
	        if (mysql_num_rows($resultp) !== 1) {
	           mysql_close();
	           $out[] = $person_id;
	           return $out;
	        }
	        $out[] = mysql_fetch_array($resultp, MYSQL_ASSOC);
	      }
        mysql_close();
        return $out;
	}
    /*
	 * @return ids of all persons scheduled for a particular venue, group, day, and time
     */
	function get_person_ids($venue, $group, $day, $time) {
        connect();
		$query1 = "SELECT * FROM dbSchedules WHERE venue = '" .
				               $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return array("Error: group-day-time not valid");
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    $person_ids = explode(',', $result_row['persons']);
	    mysql_close();
	    return $person_ids;
	}
    /*
	 * @return number of slots for a particular venue, group, day, and time
	 * this is fixed with a kluge.
     */
	function get_total_slots($venue, $group, $day, $time) {
        connect();
		$query1 = "SELECT * FROM dbSchedules WHERE venue = '" .
				               $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return "Error: group-day-time not valid";
	    }
	    $result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	    mysql_close();
	    $persons = get_persons($venue, $group, $day, $time);
	    if (sizeof($persons)>$result_row['slots'])
	        return sizeof($persons);
	    else
	    	return $result_row['slots'];
	}
    /*
	 * @return number of vacancies for a particular venue, group, day, and time
     */
	function get_total_vacancies($venue,$group,$day,$time) {
		$slots=get_total_slots($venue,$group,$day,$time);
		$persons=count(get_persons($venue,$group,$day,$time));
		return $slots-$persons;
	}
    /*
	 * @return number of vacancies for a particular venue, group, day, and time
     */
	function check_valid_schedule($venue, $group, $day, $time) {
      connect();
		$query1 = "SELECT * FROM dbSchedules WHERE venue = '" .
				               $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
		mysql_close();
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       return false;
	    }
	    return true;
	}
	/*
	 * @return number of vacancies for a particular venue, group, day, and time
     */
	function edit_schedule_vacancy($venue, $group,$day,$time,$change) {
		connect();
		$query1 = "SELECT * FROM dbSchedules WHERE venue = '" .
				               $venue . "' AND my_group = '" .
				               $group . "' AND day = '" .
				               $day . "' AND time = '" . $time . "'";
		$result = mysql_query($query1);
	    if (!$result) die ("could not query the database");
	    if (mysql_num_rows($result) !== 1) {
	       mysql_close();
	       return false;
	    }
		 $result_row=mysql_fetch_array($result,MYSQL_ASSOC);
		 $result_row['slots']=$result_row['slots']+$change;
		 mysql_query("UPDATE dbSchedules SET slots = '". $result_row['slots'] . 
		 		"' WHERE venue = '" . $venue . "' AND my_group = '" . $group . 
		 		"' AND day = '" . $day . "' AND time = '" . $time . "'");
		 mysql_close();
	    return true;
	}
    

?>
